package sgc.beans;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import sgc.struc.Cecos;

@ManagedBean(name = "cecosBean")
@SessionScoped
public class CecosBean implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private DataSource ds;

	public CecosBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	
	private int act_company() {
		int company;
		company = 0;
		try {
			FacesContext context = FacesContext.getCurrentInstance();
			CheckUserBean cu = (CheckUserBean) context.getApplication()
					.evaluateExpressionGet(context, "#{checkUserBean}",
							CheckUserBean.class);
			company = cu.getUsuario().getCompany_code();
		} catch (Exception e) {
		}
		return company;

	}
	public List<Cecos> getCecosList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllCecos_1(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, act_company());
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<Cecos> list = new ArrayList<Cecos>();
		while (result.next()) {
			Cecos aux_cecos = new Cecos();
			aux_cecos.setId(result.getInt(1));
			aux_cecos.setName(result.getString(2));
			aux_cecos.setDescrip(result.getString(3));
			aux_cecos.setCompany(result.getString(4));
			list.add(aux_cecos);
		}
		con.close();
		return list;
	}
	
	public List<String> getNameList() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_GetAllCecos_1(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, act_company());
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<String> list = new ArrayList<String>();
		String name;
		while (result.next()) {
			name = result.getString(2) + " - " + result.getString(3);
			list.add(name);
		}
		con.close();
		return list;
	}
}
